Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


How Transactions Work

To give you a better idea of how Oracle operates, this section analyzes a sample transaction. Throughout this book, the term transaction is used to describe a logical group of work. This group of work can consist of one or many SQL statements and must end with a commit or a rollback. Because this example assumes a client/server application, SQL*Net is necessary. The following steps are executed to complete the transaction:

1.  The application processes the user input and creates a connection to the server through SQL*Net.
2.  The server picks up the connection request and creates a server process on behalf of the user.
3.  The user executes an SQL statement and commits the transaction. In this example, the user changes the value of a row in a table.
4.  The server process takes this SQL statement and checks the shared pool to see whether there is a shared SQL area that has this identical SQL statement. If it finds an identical shared SQL area, the server process checks to see whether the user has access privileges to the data. If the user has access privileges, the server process uses the shared SQL area to process the request. If a shared SQL area is not found, a new shared SQL area is allocated, the statement is parsed, and then it is executed.
5.  The server process retrieves the data from the SGA (if present) or retrieves it from the data file into the SGA.
6.  The server process modifies the data in the SGA. Remember that the server processes can only read from the data files. Because the transaction has been committed, the LGWR process immediately records the transaction in the redo log file. At some later time, the DBWR process writes the modified blocks to permanent storage.
7.  If the transaction is successful, a completion code is returned across the network to the client process. If a failure has occurred, an error message is returned.


NOTE:  A transaction is not considered committed until the write to the redo log file has been completed. This arrangement ensures that, in the event of a system failure, a committed transaction can be recovered. If a transaction has been committed, it is guaranteed to be “cast in stone.”

While transactions are occurring, the Oracle background processes are all doing their jobs, keeping the system running smoothly. Keep in mind that while this process is going on, hundreds of other users may be doing similar tasks. It is Oracle’s job to keep the system in a consistent state, to manage contention and locking, and to perform at the necessary rate.

This overview is intended to give you an understanding of the complexity and amount of interaction involved in the Oracle RDBMS. As you look at the details of tuning the server processes and applications later in this book, you can use this overview as a reference to the basics of how the Oracle RDBMS operates. Because of the differences in operating systems, minor variances in different environments will be discussed individually.

Your introduction to Oracle continues with a look at the different products Oracle offers.

Oracle Products

Oracle produces a wide range of products and services, many of which you may not be aware of. Of course, the product you are familiar with is the RDBMS product that supports over 90 platforms. Within the RDBMS server product itself are many options to choose from: the Procedural option, parallel features such as the Parallel Query option, and (in some platforms) features such as Oracle Parallel Server. Oracle has an impressive set of development tools, such as Developer/2000, designed to create applications for the Windows, Macintosh, and Motif environments. Oracle also has traditional character-based applications and applications such as the Oracle Financials suite. All these products are backed by a rich set of support options. The following sections give a general overview of the Oracle products, starting with the RDBMS product itself.

Oracle RDBMS Products

The Oracle RDBMS is the core of Oracle’s product set. Many—if not all—the products Oracle offers use the RDBMS in one way or another. Following is a list of some of the additional features available within the core RDBMS.

PL/SQL

PL/SQL is commonly known as the Procedural option (Oracle’s procedural language extension to SQL). PL/SQL adds the procedural functionality of a structured programming language to SQL. PL/SQL allows code to be stored in the database to reduce network traffic between applications and the database. This reduction in network traffic can help improve performance. Another feature of PL/SQL is its ability to control data access by allowing the users access to only PL/SQL statements; this arrangement distances the user from the data layer. To reduce network contention, users can send blocks of PL/SQL statements to the database instead of sending individual SQL statements.

SQL*Net

SQL*Net is the interface used to connect user and server processes on different machines on a network or between user processes on the same machine through a shared dispatcher process. SQL*Net includes the physical network connection through a variety of different protocols. For example, with SQL*Net and TCP/IP on your PC, you can communicate to Oracle on any platform running SQL*Net TCP/IP. By adhering to standards, any of the supported Oracle platforms can communicate with any other platform, provided that they are speaking SQL*Net with the same protocol.

The application programmer needs only to program to the SQL*Net API to provide this transparent connection. If the network protocol were to change, you only have to relink the application with the proper SQL*Net protocol(s).

Distributed Option

The Distributed option allows you to divide data among many different components in a network; using a two-phase commit, you can transparently access the data in a consistent manner. The Distributed option also allows transparent access to remote data. A two-phase commit is a mechanism in which data from separate machines can be manipulated together and is guaranteed to either be committed or rolled back together. In conjunction with the Procedural option, the Distributed option allows for read-only copies (known as snapshots) of tables to be replicated. Snapshots can be queried but not updated; they are periodically updated by the master table.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.